﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DTO;

namespace DAO
{
    public class khachhang_DAO
    {
        public static DataSet loadthongtinkhlist()
        {
            string sql = "select makh,tenkh,cmnd,ngaysinh,gioitinh,sdt,map,ngaydat from khachhang";
            DataSet ds = new DataSet();
            ds = dataconnect.laydulieu(sql);
            return ds;
        }
        public static DataSet automakh()
        {
            string sql = "select top 1 makh from khachhang order by makh desc";
            DataSet ds = new DataSet();
            ds = dataconnect.laydulieu(sql);
            return ds;
        }
        public static bool themkhvaop(khachhang_DTO kh,phong_DTO phong)
        {
            string sql = "insert into khachhang (makh,map,tenkh,ngaysinh,gioitinh,sdt,cmnd,ngaydat) values('"+kh.makh+"',"+kh.map+",'"+kh.tenkh+"','"+kh.ngaysinh+"','"+kh.gioitinh+"','"+kh.sdt+"',"+kh.cmnd+",'"+kh.ngaydat+"')";
            string sql2="update phong set tinhtrang=true where map="+phong.map+"";
            dataconnect.xulycaulenh(sql2);
            return dataconnect.xulycaulenh(sql);
        }
        public static DataSet timkiemkhachhang(khachhang_DTO kh)
        {
            string sql = "select * from khachhang where cmnd="+kh.cmnd+"";
            DataSet ds = new DataSet();
            ds = dataconnect.laydulieu(sql);
            return ds;
        }

        public static DataSet timkiemkhachhangtheoten(khachhang_DTO kh)
        {
            string sql = "select * from khachhang where tenkh like '%"+kh.tenkh+"%'";
            DataSet ds = new DataSet();
            ds = dataconnect.laydulieu(sql);
            return ds;
        }
        public static DataSet laythongtinkh()
        {
            string sql = "select makh,map,tenkh,ngaysinh,gioitinh,sdt,cmnd,ngaydat from khachhang";
            DataSet ds = new DataSet();
            ds = dataconnect.laydulieu(sql);
            return ds;
        }
        public static bool themkh(khachhang_DTO kh)
        {
            string sql = "insert into khachhang(makh,map,tenkh,ngaysinh,gioitinh,sdt,cmnd,ngaydat) values('" + kh.makh + "','" + kh.map + "','" + kh.tenkh + "','" + kh.ngaysinh + "','" + kh.gioitinh + "','" + kh.sdt + "','" + kh.cmnd + "','" + kh.ngaydat + "')";
            return dataconnect.xulycaulenh(sql);
        }
        public static bool xoakh(khachhang_DTO kh,phong_DTO p)
        {
            string sql = "delete from khachhang where makh='" + kh.makh + "'";
            string sql2 = "update phong set tinhtrang=false where map="+p.map+"";
            dataconnect.xulycaulenh(sql2);
            return dataconnect.xulycaulenh(sql);
        }
        public static bool suakh(khachhang_DTO kh)
        {
            string sql = "update khachhang set tenkh='" + kh.tenkh + "',map=" + kh.map + ",ngaysinh='" + kh.ngaysinh + "',gioitinh='" + kh.gioitinh + "',sdt='" + kh.sdt + "',cmnd=" + kh.cmnd + ",ngaydat='" + kh.ngaydat + "' where makh='" + kh.makh + "'";
            return dataconnect.xulycaulenh(sql);
        }
    }
}
